/*
 * Copyright (C) 2010 Rodion Volkov (volkov.rodion@gmail.com)
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.rchik.divermate;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;

import com.rchik.divermate.R;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DiveTablesDBAdapter {
	
	public static final String KEY_DIVINGTABLES_TITLE = "title";
    public static final String KEY_DIVINGTABLES_ROWID = "_id";
    public static final String KEY_NITRO_DEPTH = "depth";
	public static final String KEY_NITRO_TIME = "time";
	public static final String KEY_NITRO_TABLEID = "table_id";
	public static final String KEY_NITRO_NITROLEVEL = "nitro_level";
    public static final String KEY_NITRO_ROWID = "_id";
    public static final String KEY_COEFFICIENTS_ROWID = "_id";
    public static final String KEY_COEFFICIENTS_TABLEID = "table_id";
    public static final String KEY_COEFFICIENTS_NITROLEVEL = "nitro_level";
    public static final String KEY_COEFFICIENTS_DEPTH = "depth";
    public static final String KEY_COEFFICIENTS_ADDEDTIME = "added_time";
    public static final String KEY_COEFFICIENTS_MAXTIME = "max_time";
    public static final String KEY_REST_ROWID = "_id";
    public static final String KEY_REST_TABLEID = "table_id";
    public static final String KEY_REST_LEVEL_BEFORE = "level_before";
    public static final String KEY_REST_LAST_ACTUAL_TIME = "last_actual_time";
    public static final String KEY_REST_LEVEL_AFTER = "level_after";
    public static final String KEY_SAFETY_ROWID = "_id";
    public static final String KEY_SAFETY_TABLEID = "table_id";
    public static final String KEY_SAFETY_DEPTH = "depth";
    public static final String KEY_SAFETY_NITROLEVEL = "nitro_level";
    public static final String KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME = "req_safety_stop_time";

    private static final String TAG = "DiveTablesDBAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    /**
     * Database creation sql statements
     */
    private static final String DIVINGTABLES_DATABASE_CREATE =
            "create table divingtables (_id integer primary key autoincrement, "
                    + "title text not null);";
    
    private static final String NITROLEVEL_DATABASE_CREATE =
            "create table nitrolevel (_id integer primary key autoincrement, "
    				+ "table_id integer,"
                    + "depth integer,"
                    + "time integer,"
                    + "nitro_level integer);";
    private static final String COEFFICIENTS_DATABASE_CREATE =
    		"create table coefficients (_id integer primary key autoincrement, "
    				+ "table_id integer,"
    				+ "nitro_level integer,"
    				+ "depth integer,"
    				+ "added_time integer,"
    				+ "max_time integer);";
    private static final String REST_DATABASE_CREATE =
    		"create table rest (_id integer primary key autoincrement, "
    				+ "table_id integer,"
    				+ "level_before integer,"
    				+ "last_actual_time integer,"
    				+ "level_after integer);";
    
    public static final String SAFETY_DATABASE_CREATE = 
	    	"create table safetystops (_id integer primary key autoincrement, "
					+ "table_id integer,"
			        + "depth integer,"
			        + "nitro_level integer,"
			        + "req_safety_stop_time integer);";

    private static final String DATABASE_NAME = "data";
    private static final String DIVINGTABLES_DATABASE_TABLE = "divingtables";
    private static final String NITROLEVEL_DATABASE_TABLE = "nitrolevel";
    private static final String COEFFICIENTS_DATABASE_TABLE = "coefficients";
    private static final String REST_DATABASE_TABLE = "rest";
    private static final String SAFETY_DATABASE_TABLE = "safetystops";
    private static final int DATABASE_VERSION = 2;

    public static Context mCtx;
     
    public class DataNotPresentException extends Exception {
    	
    	/**
		 * 
		 */
		private static final long serialVersionUID = 805356828053346542L;

		@SuppressWarnings("unused")
		private DataNotPresentException()
    	{
    		super();
    	}
    	
    	public DataNotPresentException(String detailMessage)
    	{
    		super(detailMessage);
    	}
    }
    
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DIVINGTABLES_DATABASE_CREATE);
            db.execSQL(NITROLEVEL_DATABASE_CREATE);
            db.execSQL(COEFFICIENTS_DATABASE_CREATE);
            db.execSQL(REST_DATABASE_CREATE);
            db.execSQL(SAFETY_DATABASE_CREATE);
            
            FillTableNames(db);
            
            FillNitrolevelTable(db);
            
            FillCoefficientsTable(db);
            
            FillRestTable(db);
            
            FillSafetyTable(db);
        }
        
        private void FillTableNames(SQLiteDatabase db)
        {
        	InputStream databaseNamesInputStream = mCtx.getResources().openRawResource(R.raw.diving_table_names);
            InputStreamReader isr = new InputStreamReader(databaseNamesInputStream);
            BufferedReader br = new BufferedReader(isr);
            
            String line;
            
            try
            {
            	do 
            	{
            		line = br.readLine();
            		
            		if ( line != null )
            		{
	                    ContentValues initialValues = new ContentValues();
	                    initialValues.put(KEY_DIVINGTABLES_TITLE, line);
	
	                    db.insert(DIVINGTABLES_DATABASE_TABLE, null, initialValues);
            		}
            	}
            	while (line != null);
            }
            catch(Exception e)
            {
            	Log.w("DBCreate", "Exception during stream procession");
            }
        }
        
        private void FillNitrolevelTable(SQLiteDatabase db)
        {
        	InputStream nitroLevelsInputStream = mCtx.getResources().openRawResource(R.raw.nitrolevels);
        	InputStreamReader isr = new InputStreamReader(nitroLevelsInputStream);
            BufferedReader br = new BufferedReader(isr);
            String line;
            
            try
            {
            	do 
            	{
            		line = br.readLine();
            		
            		if ( line != null )
            		{
	            		String[] values = line.split(",");
	                    ContentValues initialValues = new ContentValues();
	                    initialValues.put(KEY_NITRO_TABLEID, values[0]);
	                    initialValues.put(KEY_NITRO_DEPTH, values[1]);
	                    initialValues.put(KEY_NITRO_TIME, values[2]);
	                    initialValues.put(KEY_NITRO_NITROLEVEL, values[3]);
	
	                    db.insert(NITROLEVEL_DATABASE_TABLE, null, initialValues);
            		}
            	}
            	while (line != null);
            }
            catch(Exception e)
            {
            	Log.w("DBCreate", "Exception during stream procession in nitro");
            }
        }
        
        private void FillCoefficientsTable(SQLiteDatabase db)
        {
        	InputStream coefficientsInputStream = mCtx.getResources().openRawResource(R.raw.coefficients);
        	InputStreamReader isr = new InputStreamReader(coefficientsInputStream);
            BufferedReader br = new BufferedReader(isr);
            String line;
            
            try
            {
            	do 
            	{
            		line = br.readLine();
            		
            		if ( line != null )
            		{
	            		String[] values = line.split(",");
	                    ContentValues initialValues = new ContentValues();
	                    initialValues.put(KEY_COEFFICIENTS_TABLEID, values[0]);
	                    initialValues.put(KEY_COEFFICIENTS_NITROLEVEL, values[1]);
	                    initialValues.put(KEY_COEFFICIENTS_DEPTH, values[2]);
	                    initialValues.put(KEY_COEFFICIENTS_ADDEDTIME, values[3]);
	                    initialValues.put(KEY_COEFFICIENTS_MAXTIME, values[4]);
	
	                    db.insert(COEFFICIENTS_DATABASE_TABLE, null, initialValues);
            		}
            	}
            	while (line != null);
            }
            catch(Exception e)
            {
            	Log.w("DBCreate", "Exception during stream procession in coefficients");
            }
        }
        
        private void FillRestTable(SQLiteDatabase db)
        {
        	InputStream restInputStream = mCtx.getResources().openRawResource(R.raw.rest);
        	InputStreamReader isr = new InputStreamReader(restInputStream);
            BufferedReader br = new BufferedReader(isr);
            String line;
            
            try
            {
            	do 
            	{
            		line = br.readLine();
            		
            		if ( line != null )
            		{
	            		String[] values = line.split(",");
	                    ContentValues initialValues = new ContentValues();
	                    initialValues.put(KEY_REST_TABLEID, values[0]);
	                    initialValues.put(KEY_REST_LEVEL_BEFORE, values[1]);
	                    initialValues.put(KEY_REST_LAST_ACTUAL_TIME, values[2]);
	                    initialValues.put(KEY_REST_LEVEL_AFTER, values[3]);
	
	                    db.insert(REST_DATABASE_TABLE, null, initialValues);
            		}
            	}
            	while (line != null);
            }
            catch(Exception e)
            {
            	Log.w("DBCreate", "Exception during stream procession in rest");
            }
        }
        
        private void FillSafetyTable(SQLiteDatabase db)
        {
        	InputStream safetyInputStream = mCtx.getResources().openRawResource(R.raw.safety);
        	InputStreamReader isr = new InputStreamReader(safetyInputStream);
        	BufferedReader br = new BufferedReader(isr);
        	String line;
            
            try
            {
            	do 
            	{
            		line = br.readLine();
            		
            		if ( line != null )
            		{
	            		String[] values = line.split(",");
	                    ContentValues initialValues = new ContentValues();
	                    initialValues.put(KEY_SAFETY_TABLEID, values[0]);
	                    initialValues.put(KEY_SAFETY_DEPTH, values[1]);
	                    initialValues.put(KEY_SAFETY_NITROLEVEL, values[2]);
	                    initialValues.put(KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME, values[3]);
	
	                    db.insert(SAFETY_DATABASE_TABLE, null, initialValues);
            		}
            	}
            	while (line != null);
            }
            catch(Exception e)
            {
            	Log.w("DBCreate", "Exception during stream procession in safety");
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS divingtables");
            db.execSQL("DROP TABLE IF EXISTS nitrolevel");
            db.execSQL("DROP TABLE IF EXISTS coefficients");
            db.execSQL("DROP TABLE IF EXISTS rest");
            db.execSQL("DROP TABLE IF EXISTS safetystops");
            onCreate(db);
        }
    }
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public DiveTablesDBAdapter(Context ctx) {
    	DiveTablesDBAdapter.mCtx = ctx;
    }
    
    /**
     * Open the dive tables database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public DiveTablesDBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        
        return this;
    }
    
    public void close() {
        mDbHelper.close();
    }
    
    /**
     * Create a new diving table using the title provided. If the table is
     * successfully created return the new rowId for that table, otherwise return
     * a -1 to indicate failure.
     * 
     * @param title the title of the table
     * @return rowId or -1 if failed
     */
    public long createDivingTable(String title) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_DIVINGTABLES_TITLE, title);

        return mDb.insert(DIVINGTABLES_DATABASE_TABLE, null, initialValues);
    }
    
    /**
     * Create a new nitro level entry using the data provided. If the entry is
     * successfully created return the new rowId for that entry, otherwise return
     * a -1 to indicate failure.
     * 
     * @param table_id the id of the diving table
     * @param depth the depth of the diving
     * @param nitro_level nitro level this entry refers to
     * @param req_safety_stop_time the time of the diving
     * @return rowId or -1 if failed
     */
    public long createSafetyStopsEntry(int table_id, int depth, int nitro_level, int req_safety_stop ) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_SAFETY_TABLEID, table_id);
        initialValues.put(KEY_SAFETY_DEPTH, depth);
        initialValues.put(KEY_SAFETY_NITROLEVEL, nitro_level);
        initialValues.put(KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME, req_safety_stop);

        return mDb.insert(SAFETY_DATABASE_TABLE, null, initialValues);
    }
    
    /**
     * Create a new coefficient entry using the data provided. If the entry is
     * successfully created return the new rowId for that entry, otherwise return
     * a -1 to indicate failure.
     * 
     * @param table_id the id of the diving table
     * @param nitro_level the current nitro level 
     * @param depth the depth of the diving
     * @param added_time the time of the diving to add
     * @param max_time the maximum remaining dive time on this level
     * @return rowId or -1 if failed
     */
    public long createCoefficientEntry(int table_id, int nitro_level, int depth, int added_time, int max_time  ) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_COEFFICIENTS_TABLEID, table_id);
        initialValues.put(KEY_COEFFICIENTS_NITROLEVEL, nitro_level);
        initialValues.put(KEY_COEFFICIENTS_DEPTH, depth);
        initialValues.put(KEY_COEFFICIENTS_ADDEDTIME, added_time);
        initialValues.put(KEY_COEFFICIENTS_MAXTIME, max_time);
        

        return mDb.insert(COEFFICIENTS_DATABASE_TABLE, null, initialValues);
    }
    
    /**
     * Create a new rest entry using the data provided. If the entry is
     * successfully created return the new rowId for that entry, otherwise return
     * a -1 to indicate failure.
     * 
     * @param table_id the id of the diving table
     * @param level_before the nitro level before the rest 
     * @param last_actual_time the last time the current entry is still actual
     * @param level_after the resulting nitro level after the rest
     * @return rowId or -1 if failed
     */
    public long createRestEntry(int table_id, int level_before, int last_actual_time, int level_after) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_REST_TABLEID, table_id);
        initialValues.put(KEY_REST_LEVEL_BEFORE, level_before);
        initialValues.put(KEY_REST_LAST_ACTUAL_TIME, last_actual_time);
        initialValues.put(KEY_REST_LEVEL_AFTER, level_after);
        
        return mDb.insert(REST_DATABASE_TABLE, null, initialValues);
    }
    
    /**
     * Create a new safety entry using the data provided. If the entry is
     * successfully created return the new rowId for that entry, otherwise return
     * a -1 to indicate failure.
     * 
     * @param table_id the id of the diving table
     * @param depth the depth of the diving
     * @param time the time of the diving
     * @param nitro_level the resulting nitro level
     * @return rowId or -1 if failed
     */
    public long createNitroLevelEntry(int table_id, int depth, int time, int nitro_level  ) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NITRO_TABLEID, table_id);
        initialValues.put(KEY_NITRO_DEPTH, depth);
        initialValues.put(KEY_NITRO_TIME, time);
        initialValues.put(KEY_NITRO_NITROLEVEL, nitro_level);

        return mDb.insert(NITROLEVEL_DATABASE_TABLE, null, initialValues);
    }
    
    /**
     * Delete the diving table with the given rowId
     * 
     * @param rowId id of diving table to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteDivingTable(long rowId) {

        return mDb.delete(DIVINGTABLES_DATABASE_TABLE, KEY_DIVINGTABLES_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Delete the nitro level entry with the given rowId
     * 
     * @param rowId id of nitro level entry to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteNitroLevelEntry(long rowId) {

        return mDb.delete(NITROLEVEL_DATABASE_TABLE, KEY_NITRO_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Delete the coefficients entry with the given rowId
     * 
     * @param rowId id of coefficients entry to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteCoefficientsEntry(long rowId) {

        return mDb.delete(COEFFICIENTS_DATABASE_TABLE, KEY_COEFFICIENTS_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Delete the rest entry with the given rowId
     * 
     * @param rowId id of rest entry to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteRestEntry(long rowId) {

        return mDb.delete(REST_DATABASE_TABLE, KEY_REST_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Delete the safety entry with the given rowId
     * 
     * @param rowId id of rest entry to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteSafetyEntry(long rowId) {

        return mDb.delete(SAFETY_DATABASE_TABLE, KEY_SAFETY_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Return a Cursor over the list of all diving tables in the database
     * 
     * @return Cursor over all diving tables
     */
    public Cursor fetchAllTables() {

        return mDb.query(DIVINGTABLES_DATABASE_TABLE, new String[] {KEY_DIVINGTABLES_ROWID, KEY_DIVINGTABLES_TITLE}, null, null, null, null, null);
    }
    
    /**
     * Return a Cursor over the list of all nitro level entries in the database
     * 
     * @return Cursor over all nitro level entries
     */
    public Cursor fetchAllNitroLevelEntries() {

        return mDb.query(NITROLEVEL_DATABASE_TABLE, new String[] {KEY_NITRO_ROWID, KEY_NITRO_TABLEID, KEY_NITRO_DEPTH, KEY_NITRO_TIME, KEY_NITRO_NITROLEVEL}, null, null, null, null, null);
    }
    
    /**
     * Return a Cursor over the list of all coefficient entries in the database
     * 
     * @return Cursor over all coefficient entries
     */
    public Cursor fetchAllCoefficientEntries() {

        return mDb.query(COEFFICIENTS_DATABASE_TABLE, new String[] {KEY_COEFFICIENTS_ROWID, 
        		KEY_COEFFICIENTS_TABLEID, KEY_COEFFICIENTS_NITROLEVEL, 
        		KEY_COEFFICIENTS_DEPTH, KEY_COEFFICIENTS_ADDEDTIME,
        		KEY_COEFFICIENTS_MAXTIME}, null, null, null, null, null);
    }
    
    /**
     * Return a Cursor over the list of all rest entries in the database
     * 
     * @return Cursor over all rest entries
     */
    public Cursor fetchAllRestEntries() {

        return mDb.query(REST_DATABASE_TABLE, new String[] {KEY_REST_ROWID, 
        		KEY_REST_TABLEID, KEY_REST_LEVEL_BEFORE, 
        		KEY_REST_LAST_ACTUAL_TIME, KEY_REST_LEVEL_AFTER}, 
        		null, null, null, null, null);
    }
    
    /**
     * Return a Cursor over the list of all safety  entries in the database
     * 
     * @return Cursor over all safety entries
     */
    public Cursor fetchAllSafetyStopEntries() {

        return mDb.query(SAFETY_DATABASE_TABLE, new String[] {KEY_SAFETY_ROWID, 
        		KEY_SAFETY_TABLEID, 
        		KEY_SAFETY_DEPTH,
        		KEY_SAFETY_NITROLEVEL,
        		KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME}, null, null, null, null, null);
    }
    
    /**
     * Return a Cursor positioned at the diving table that matches the given rowId
     * 
     * @param rowId id of the diving table to retrieve
     * @return Cursor positioned to matching table, if found
     * @throws SQLException if table could not be found/retrieved
     */
    public Cursor fetchTable(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, DIVINGTABLES_DATABASE_TABLE, new String[] {KEY_DIVINGTABLES_ROWID,
                        KEY_DIVINGTABLES_TITLE}, KEY_DIVINGTABLES_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor.getCount() > 0) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    
    /**
     * Return a Cursor positioned at the nitro level entry that matches the given rowId
     * 
     * @param rowId id of the nitro level entry to retrieve
     * @return Cursor positioned to matching nitro level entry, if found
     * @throws SQLException if entry could not be found/retrieved
     */
    public Cursor fetchNitroLevelEntry(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, NITROLEVEL_DATABASE_TABLE, new String[] {KEY_NITRO_ROWID,
                        KEY_NITRO_TABLEID, KEY_NITRO_DEPTH, KEY_NITRO_TIME, KEY_NITRO_NITROLEVEL}, KEY_NITRO_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor.getCount() > 0) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    
    /**
     * Return a Cursor positioned at the coefficient entry that matches the given rowId
     * 
     * @param rowId id of the coefficient entry to retrieve
     * @return Cursor positioned to matching coefficient entry, if found
     * @throws SQLException if entry could not be found/retrieved
     */
    public Cursor fetchCoefficientEntry(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, COEFFICIENTS_DATABASE_TABLE, new String[] {KEY_COEFFICIENTS_ROWID,
                        KEY_COEFFICIENTS_TABLEID, KEY_COEFFICIENTS_NITROLEVEL, KEY_COEFFICIENTS_DEPTH, 
                        KEY_COEFFICIENTS_ADDEDTIME, KEY_COEFFICIENTS_MAXTIME}, KEY_COEFFICIENTS_ROWID + 
                        "=" + rowId, null, null, null, null, null);
        if (mCursor.getCount() > 0) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    
    /**
     * Return a Cursor positioned at the rest entry that matches the given rowId
     * 
     * @param rowId id of the rest entry to retrieve
     * @return Cursor positioned to matching rest entry, if found
     * @throws SQLException if entry could not be found/retrieved
     */
    public Cursor fetchRestEntry(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, REST_DATABASE_TABLE, new String[] {KEY_REST_ROWID,
                        KEY_REST_TABLEID, KEY_REST_LEVEL_BEFORE, KEY_REST_LAST_ACTUAL_TIME, 
                        KEY_REST_LEVEL_AFTER}, KEY_REST_ROWID + 
                        "=" + rowId, null, null, null, null, null);
        if (mCursor.getCount() > 0) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    
    /**
     * Return a Cursor positioned at the safety entry that matches the given rowId
     * 
     * @param rowId id of the safety entry to retrieve
     * @return Cursor positioned to matching rest entry, if found
     * @throws SQLException if entry could not be found/retrieved
     */
    public Cursor fetchSafetyEntry(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, SAFETY_DATABASE_TABLE, new String[] {KEY_SAFETY_ROWID,
                        KEY_SAFETY_TABLEID, 
                        KEY_SAFETY_DEPTH,
                        KEY_SAFETY_NITROLEVEL,
                        KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME}, KEY_SAFETY_ROWID + 
                        "=" + rowId, null, null, null, null, null);
        if (mCursor.getCount() > 0) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    
    /**
     * Return nitro level of the best matching entry in the database
     * 
     * @param table_id table_id of the entry to retrieve
     * @param depth the depth of the dive
     * @param time the time of the dive
     * @return matching nitro level, if found, -1 otherwise
     * @throws DataNotPresentException if one of the entries isn't found
     */
    public int fetchTheAppropriateNitroLevel(int table_id, int depth, int time) throws DataNotPresentException {
    	
    	int resultTime, resultDepth;
	    
    	Cursor mCursor;
    	
    	try
    	{
	    	mCursor =
	
	            mDb.query(true, NITROLEVEL_DATABASE_TABLE, new String[] {KEY_NITRO_DEPTH}, "( " + 
	            		KEY_NITRO_DEPTH + ">=" + depth + " AND " + KEY_NITRO_TABLEID + "=" + 
	            		table_id + " )", null, null, null, KEY_NITRO_DEPTH + " ASC", null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        resultDepth = mCursor.getInt(mCursor.getColumnIndex(KEY_NITRO_DEPTH));
		    }
		    else
		    	throw new DataNotPresentException("Nitro-Depth");
    	}
    	catch (SQLException e)
    	{
    		throw new DataNotPresentException("Nitro-Depth");
    	}
	    
    	try
    	{
	    	mCursor =
	
	            mDb.query(true, NITROLEVEL_DATABASE_TABLE, new String[] {KEY_NITRO_TIME}, 
	            		"( " + KEY_NITRO_TIME + ">=" + time + " AND " + 
	            		KEY_NITRO_TABLEID + "=" + table_id + " AND " +
	            		KEY_NITRO_DEPTH + "=" + resultDepth + " )", null,
	                    null, null, KEY_NITRO_TIME + " ASC", null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        resultTime = mCursor.getInt(mCursor.getColumnIndex(KEY_NITRO_TIME));
		    }
		    else
		    	throw new DataNotPresentException("Nitro-Time");
    	}
    	catch (SQLException e)
    	{
    		throw new DataNotPresentException("Nitro-Time");
    	}
	    
    	try
    	{
		    mCursor =
	
	            mDb.query(true, NITROLEVEL_DATABASE_TABLE, new String[] {KEY_NITRO_NITROLEVEL}, "( " + 
	            		KEY_NITRO_DEPTH + "=" + resultDepth + " AND " + KEY_NITRO_TABLEID + 
	            		"=" + table_id + " AND " + KEY_NITRO_TIME + "=" + resultTime +
	            		" )"  , null, null, null, null, null);
		    
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		        
		        return mCursor.getInt(mCursor.getColumnIndex(KEY_NITRO_NITROLEVEL));
		    }
		    else
		    	throw new DataNotPresentException("Nitro-Nitro");
    	}
    	catch (SQLException e)
    	{
    		throw new DataNotPresentException("Nitro-Nitro");
    	}
    }
    
    /**
     * Return the appropriate coefficients entry for the given table_id, nitro 
     * level and depth
     * @param table_id the id of the diving table
     * @param nitro_level the initial nitro level prior to the dive
     * @param depth the depth of the following dive
     * @return the pair of numbers - added time coefficient and a maximum time 
     * @throws DataNotPresentException if some data is not present in tables
     */
    public Coefficients fetchTheAppropriateCoefficients( int table_id, int nitro_level, int depth ) throws DataNotPresentException
    {
    	Coefficients result = null;
    	int resultDepth;
    	
    	Cursor mCursor;
    	
    	try
    	{
	    	mCursor =
	
	            mDb.query(true, COEFFICIENTS_DATABASE_TABLE, new String[] {KEY_COEFFICIENTS_DEPTH}, 
	            		"( " + KEY_COEFFICIENTS_DEPTH + ">=" + depth + " AND " + 
	            		KEY_COEFFICIENTS_TABLEID + "=" + table_id + " AND " +
	            		KEY_COEFFICIENTS_NITROLEVEL + "=" + nitro_level + " )", null,
	                    null, null, KEY_COEFFICIENTS_DEPTH + " ASC", null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        resultDepth = mCursor.getInt(mCursor.getColumnIndex(KEY_COEFFICIENTS_DEPTH));
		    }
		    else
		    	throw new DataNotPresentException("Coeff-Depth");
    	}
	    catch ( SQLException e )
	    {
	    	throw new DataNotPresentException("Coeff-Depth");
	    }
	    
	    try
	    {
		    mCursor =
	
	            mDb.query(true, COEFFICIENTS_DATABASE_TABLE, new String[] {KEY_COEFFICIENTS_ADDEDTIME, 
	            		KEY_COEFFICIENTS_MAXTIME},"( " + KEY_COEFFICIENTS_NITROLEVEL + "=" + nitro_level +
	            		" AND " + KEY_COEFFICIENTS_TABLEID + "=" + table_id + " AND " +
	            		KEY_COEFFICIENTS_DEPTH + "=" + resultDepth + " )", null, null, null, null, null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        result = new Coefficients();
		        result.added_time = mCursor.getInt(mCursor.getColumnIndex(KEY_COEFFICIENTS_ADDEDTIME));
		        result.max_time = mCursor.getInt(mCursor.getColumnIndex(KEY_COEFFICIENTS_MAXTIME));
		        return result;
		    }
		    else
		    	throw new DataNotPresentException("Coeff-Coeff");
	    }
	    catch ( SQLException e )
	    {
	    	throw new DataNotPresentException("Coeff-Coeff");
	    }
    }
    
    
    /**
     * Gets the nitrogen level after the given rest time with the given initial
     * nitrogen level
     * @param table_id id of the table we are working with
     * @param level_before the initial nitrogen level
     * @param restTime time of the rest on the surface
     * @return the result nitrogen level
     * @throws DataNotPresentException if some data is not present in table
     */
    public int fetchTheAppropriateRestNitroLevel(int table_id, int level_before, int restTime) throws DataNotPresentException
    {
    	int resultTime,resultLevel;
    	
    	Cursor mCursor;
    	
    	try
    	{
	    	mCursor =
	
	            mDb.query(true, REST_DATABASE_TABLE, new String[] {KEY_REST_LAST_ACTUAL_TIME,KEY_REST_LEVEL_AFTER}, "( " + 
	            		KEY_REST_LAST_ACTUAL_TIME + "<" + restTime + " AND " + KEY_REST_TABLEID + "=" + 
	            		table_id + " AND " + KEY_REST_LEVEL_BEFORE + "=" + level_before + " )", null, null, null, KEY_REST_LAST_ACTUAL_TIME + " DESC", null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        resultTime = mCursor.getInt(mCursor.getColumnIndex(KEY_REST_LAST_ACTUAL_TIME));
		        resultLevel = mCursor.getInt(mCursor.getColumnIndex(KEY_REST_LEVEL_AFTER));
		        
		        //if we are later than A level then we have no level
		        if ( resultLevel == 0 ) return -1; 
		    }
		    else throw new DataNotPresentException("Rest-Time");
    	}
    	catch ( SQLException e )
    	{
    		throw new DataNotPresentException("Rest-Time");
    	}
	    
    	try
    	{
	    	mCursor =
	
	            mDb.query(true, REST_DATABASE_TABLE, new String[] {KEY_REST_LAST_ACTUAL_TIME, 
	            		KEY_REST_LEVEL_AFTER}, "( " + KEY_REST_LAST_ACTUAL_TIME +
	            		">" + resultTime + " AND " + KEY_REST_TABLEID + "=" + 
	            		table_id + " AND " + KEY_REST_LEVEL_BEFORE + "=" + level_before + " )", null, null, null, KEY_REST_LAST_ACTUAL_TIME + " ASC", null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        return mCursor.getInt(mCursor.getColumnIndex(KEY_REST_LEVEL_AFTER));
		    }
		    else throw new DataNotPresentException("Rest-Rest");
    	}
    	catch (SQLException e)
    	{
    		throw new DataNotPresentException("Rest-Rest");
    	}
    }
    
    /**
     * 
     * @param table_id id of the table to use
     * @param depth depth of the dive
     * @param nitro_level current nitro level after dive
     * @return resulting safety conditions
     * @throws DataNotPresentException 
     */
    public SafetyRequirement fetchSafetyConditionsForTheDepthAndLevel(int table_id, int depth, int nitro_level) throws DataNotPresentException
    {
    	Cursor mCursor;
    	int resultDepth;
    	
    	try
    	{
	    	mCursor =
	
	            mDb.query(true, SAFETY_DATABASE_TABLE, new String[] {KEY_SAFETY_DEPTH}, 
	            		"( " + KEY_SAFETY_DEPTH + ">=" + depth + " AND " + 
	            		KEY_SAFETY_TABLEID + "=" + table_id + " )", null,
	                    null, null, KEY_SAFETY_DEPTH + " ASC", null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        resultDepth = mCursor.getInt(mCursor.getColumnIndex(KEY_SAFETY_DEPTH));
		    }
		    else
		    	throw new DataNotPresentException("Safety-Depth");
    	}
	    catch ( SQLException e )
	    {
	    	throw new DataNotPresentException("Safety-Depth");
	    }
    	
    	try
    	{
	    	mCursor =
	
	            mDb.query(true, SAFETY_DATABASE_TABLE, new String[] 
	                    {KEY_SAFETY_DEPTH, KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME}, 
	                    "( " + KEY_SAFETY_DEPTH + "=" + resultDepth + 
	                    " AND " + KEY_SAFETY_TABLEID + "=" + 
	            		table_id  + " AND " + KEY_SAFETY_NITROLEVEL + "<=" +
	            		nitro_level + " )", null, null, null, KEY_SAFETY_NITROLEVEL + " DESC", null);
	    	
		    if (mCursor.getCount() > 0) {
		        mCursor.moveToFirst();
		    
		        SafetyRequirement currReq = new SafetyRequirement(
		        		mCursor.getInt(mCursor.getColumnIndex(
		        				KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME)));
		        
		        return currReq;
		    }
		    else throw new DataNotPresentException("Safety-Safety");
    	}
    	catch (SQLException e)
    	{
    		throw new DataNotPresentException("Safety-Safety");
    	}
    }
    
    /**
     * 
     * @param table_id id of the table to use
     * @param depth depth of the next dive
     * @param time time of the next dive
     * @param nitro_level current nitrogen level
     * @return time to stay on the surface
     * @throws DataNotPresentException 
     */
    public int fetchTheRequiredSurfaceTime(int table_id, int depth, int time, int nitro_level) throws DataNotPresentException
    {
    	try
	    {
    		int resultDepth, resultNitro;
		    Cursor mCursor =
	
	            mDb.query(true, COEFFICIENTS_DATABASE_TABLE, new String[] {KEY_COEFFICIENTS_DEPTH},
	            		"( " + KEY_COEFFICIENTS_DEPTH + ">=" + depth +
	            		" AND " + KEY_COEFFICIENTS_TABLEID + "=" + table_id +" )", 
	            		null, null, null, null, null);
	    	
		    if (mCursor.getCount() > 0) 
		    {
		        mCursor.moveToFirst();
		        resultDepth = mCursor.getInt(mCursor.getColumnIndex(KEY_COEFFICIENTS_DEPTH));
		    }
		    else
		    {
		    	throw new DataNotPresentException("Surface-Depth");
		    }
		    
		    mCursor =
		    	
	            mDb.query(true, COEFFICIENTS_DATABASE_TABLE, new String[] {KEY_COEFFICIENTS_NITROLEVEL},
	            		"( " + KEY_COEFFICIENTS_DEPTH + "=" + resultDepth +
	            		" AND " + KEY_COEFFICIENTS_TABLEID + "=" + table_id + 
	            		" AND " + KEY_COEFFICIENTS_MAXTIME + ">=" + time + " )", 
	            		null, null, null, KEY_COEFFICIENTS_MAXTIME + " ASC", null);
		    
		    if (mCursor.getCount() > 0) 
		    {
		        mCursor.moveToFirst();
		        resultNitro = mCursor.getInt(mCursor.getColumnIndex(KEY_COEFFICIENTS_NITROLEVEL));
		    }
		    else
		    {
		    	throw new DataNotPresentException("Surface-Level");
		    }
		    
		    if ( resultNitro >= nitro_level ) return 0;
		    
		    mCursor =
		    	
	            mDb.query(true, REST_DATABASE_TABLE, new String[] {KEY_REST_LAST_ACTUAL_TIME},
	            		"( " + KEY_REST_LEVEL_BEFORE + "=" + nitro_level +
	            		" AND " + KEY_COEFFICIENTS_TABLEID + "=" + table_id + 
	            		" AND " + KEY_REST_LEVEL_AFTER + "=" + (resultNitro+1) + " )", 
	            		null, null, null, null, null);
		    
		    if (mCursor.getCount() > 0) 
		    {
		        mCursor.moveToFirst();
		        return mCursor.getInt(mCursor.getColumnIndex(KEY_REST_LAST_ACTUAL_TIME))+1;
		    }
		    else
		    {
		    	throw new DataNotPresentException("Surface-LastTime");
		    }
	    }
    	catch (SQLException e)
    	{
    		throw new DataNotPresentException("Surface-Surface");
    	}
    }
    
    /**
     * Update the diving table title using the details provided. The table to be updated is
     * specified using the rowId, and it is altered to use the title 
     * value passed in
     * 
     * @param rowId id of table to update
     * @param title value to set table title to
     * @return true if the title was successfully updated, false otherwise
     */
    public boolean updateTable(long rowId, String title) {
        ContentValues args = new ContentValues();
        args.put(KEY_DIVINGTABLES_TITLE, title);

        return mDb.update(DIVINGTABLES_DATABASE_TABLE, args, KEY_DIVINGTABLES_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Update the nitro level entry using the details provided. The entry to be updated is
     * specified using the rowId, and it is altered to use the  
     * given data passed in
     * 
     * @param rowId id of nitro level entry to update
     * @param table_id value to set table_id to
     * @param depth value to set depth to
     * @param time value to set time to
     * @param nitro_level value to set nitro_level to
     * @return true if the entry was successfully updated, false otherwise
     */
    public boolean updateNitroLevelEntry(long rowId, int table_id, int depth, int time, int nitro_level) {
        ContentValues args = new ContentValues();
        args.put(KEY_NITRO_TABLEID, table_id);
        args.put(KEY_NITRO_DEPTH, depth);
        args.put(KEY_NITRO_TIME, time);
        args.put(KEY_NITRO_NITROLEVEL, nitro_level);

        return mDb.update(NITROLEVEL_DATABASE_TABLE, args, KEY_NITRO_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Update the coefficients entry using the details provided. The entry to be updated is
     * specified using the rowId, and it is altered to use the  
     * given data passed in
     * 
     * @param rowId id of coefficients entry to update
     * @param table_id value to set table_id to
     * @param nitro_level value to set nitro_level to
     * @param depth value to set depth to
     * @param added_time value to set added_time to
     * @param max_time value to set max_time to
     * @return true if the entry was successfully updated, false otherwise
     */
    public boolean updateCoefficientsEntry(long rowId, int table_id, int nitro_level, 
    		int depth, int added_time, int max_time) {
        ContentValues args = new ContentValues();
        args.put(KEY_COEFFICIENTS_TABLEID, table_id);
        args.put(KEY_COEFFICIENTS_NITROLEVEL, nitro_level);
        args.put(KEY_COEFFICIENTS_DEPTH, depth);
        args.put(KEY_COEFFICIENTS_ADDEDTIME, added_time);
        args.put(KEY_COEFFICIENTS_MAXTIME, max_time);

        return mDb.update(COEFFICIENTS_DATABASE_TABLE, args, KEY_COEFFICIENTS_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Update the rest entry using the details provided. The entry to be updated is
     * specified using the rowId, and it is altered to use the  
     * given data passed in
     * 
     * @param rowId id of rest entry to update
     * @param table_id value to set table_id to
     * @param level_before value to set level_before to
     * @param last_actual_time value to set last_actual_time to
     * @param level_after value to set level_after to
     * @return true if the entry was successfully updated, false otherwise
     */
    public boolean updateRestEntry(long rowId, int table_id, int level_before, 
    		int last_actual_time, int level_after) {
        ContentValues args = new ContentValues();
        args.put(KEY_REST_TABLEID, table_id);
        args.put(KEY_REST_LEVEL_BEFORE, level_before);
        args.put(KEY_REST_LAST_ACTUAL_TIME, last_actual_time);
        args.put(KEY_REST_LEVEL_AFTER, level_after);

        return mDb.update(REST_DATABASE_TABLE, args, KEY_REST_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Update the safety entry using the details provided. The entry to be updated is
     * specified using the rowId, and it is altered to use the  
     * given data passed in
     * 
     * @param rowId id of safety entry to update
     * @param table_id value to set table_id to
     * @param depth value to set depth to
     * @param nitro_level nitro level which this entry refers to
     * @param req_safety_stop_time value to set required safety stop time to
     * @return true if the entry was successfully updated, false otherwise
     */
    public boolean updateSafetyEntry(long rowId, int table_id, int depth, 
    		int nitro_level, int req_safety_stop_time) {
        ContentValues args = new ContentValues();
        args.put(KEY_SAFETY_TABLEID, table_id);
        args.put(KEY_SAFETY_DEPTH, depth);
        args.put(KEY_SAFETY_NITROLEVEL, nitro_level);
        args.put(KEY_SAFETY_REQUIRED_SAFETY_STOP_TIME, req_safety_stop_time);

        return mDb.update(SAFETY_DATABASE_TABLE, args, KEY_SAFETY_ROWID + "=" + rowId, null) > 0;
    }

}